package com.svs.quartz.controller;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.apache.commons.lang3.StringUtils;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import com.svs.common.annotation.Log;
import com.svs.common.base.AjaxResult;
import com.svs.common.enums.BusinessType;
import com.svs.common.utils.ExcelUtil;
import com.svs.common.utils.data.JdbcColumnUtil;
import com.svs.framework.shiro.util.ShiroUtils;
import com.svs.framework.web.BaseController;
import com.svs.framework.web.page.TableDataInfo;
import com.svs.quartz.domain.WisDataCol;
import com.svs.quartz.domain.WisDataColInfo;
import com.svs.quartz.domain.WisDataGroup;
import com.svs.quartz.domain.WisDataSource;
import com.svs.quartz.helper.WisDataSourceHelper;
import com.svs.quartz.service.WisDataColService;
import com.svs.quartz.service.WisDataGroupService;
import com.svs.quartz.service.WisDataSourceService;

/**
 * 数据同步组 信息操作处理
 * 
 * @author 杜建桥
 * @date 2019-02-16
 */
@Controller
@RequestMapping("/quartz/wisDataGroup")
public class WisDataGroupController extends BaseController {

	private String prefix = "quartz/wisDataGroup";
	
	@Autowired
	private WisDataColService wisDataColService ;
	@Autowired
	private WisDataGroupService wisDataGroupService;
	@Autowired
	private WisDataSourceService wisDataSourceService;

	@RequiresPermissions("quartz:wisDataGroup:view")
	@GetMapping()
	public String wisDataGroup() {
		return prefix + "/wisDataGroupList";
	}

	/**
	 * 查询数据同步组列表
	 */
	@RequiresPermissions("quartz:wisDataGroup:list")
	@PostMapping("/list")
	@ResponseBody
	public TableDataInfo list(WisDataGroup wisDataGroup) {
		startPage();
		List<WisDataGroup> list = wisDataGroupService.selectAllList(wisDataGroup);
		return getDataTable(list);
	}

	/**
	 * 导出数据同步组
	 * 
	 * @param wisDataGroup
	 * @return
	 * @throws Exception
	 */
	@Log(title = "数据同步组信息导出", businessType = BusinessType.EXPORT)
	@PostMapping("/export")
	@ResponseBody
	public AjaxResult export(WisDataGroup wisDataGroup) throws Exception {
		try {
			List<WisDataGroup> list = wisDataGroupService.selectAllList(wisDataGroup);
			ExcelUtil<WisDataGroup> util = new ExcelUtil<WisDataGroup>(WisDataGroup.class);
			return util.exportExcel(list, "数据同步组");
		} catch (Exception e) {
			return error("导出Excel失败，请联系网站管理员！");
		}
	}

	/**
	 * 去导入 数据同步组 信息
	 * 
	 * @return
	 * @throws Exception
	 */
	@GetMapping("/importExcel")
	public String importExcel() throws Exception {
		return prefix + "/importWisDataGroup";
	}

	/**
	 * 数据同步组信息导入
	 * 
	 * @param file
	 */
	@RequiresPermissions("module:wisDataGroup:edit")
	@Log(title = "数据同步组", businessType = BusinessType.IMPORT)
	@PostMapping("/importExcel")
	@ResponseBody
	public AjaxResult importExcelPost(@RequestParam("file") MultipartFile file) {
		try {
			ExcelUtil<WisDataGroup> excel = new ExcelUtil<WisDataGroup>(WisDataGroup.class);
			List<WisDataGroup> results = excel.importExcel(file.getInputStream());
			int len = null != results ? results.size() : 0;
			for (int i = 0; i < len; i++) {
				WisDataGroup entity = results.get(i);
				wisDataGroupService.save(entity);
			}
			return success();
		} catch (Exception e) {
			return error();
		}
	}

	/**
	 * 新增数据同步组
	 */
	@GetMapping("/add")
	public String add(WisDataSource entity, ModelMap mmap) {
		List<WisDataSource> wisDataSourceList = wisDataSourceService.selectAllList(entity) ;
		mmap.put("wisDataSourceList", wisDataSourceList) ;
		return prefix + "/addWisDataGroup";
	}

	/**
	 * 新增保存数据同步组
	 */
	@RequiresPermissions("quartz:wisDataGroup:add")
	@Log(title = "数据同步组", businessType = BusinessType.INSERT)
	@PostMapping("/add")
	@ResponseBody
	public AjaxResult addSave(WisDataGroup wisDataGroup) {
		wisDataGroup.setCreateTime(new Date());
		wisDataGroup.setCreateBy(ShiroUtils.getLoginName());
		return toAjax(wisDataGroupService.save(wisDataGroup));
	}

	/**
	 * 编辑同步组
	 * 
	 * @param id
	 * @param mmap
	 * @return
	 */
	@GetMapping("/edit/{id}")
	public String edit(@PathVariable("id") Long id, ModelMap mmap) {
		WisDataGroup wisDataGroup = wisDataGroupService.selectByPrimaryKey(id);
		mmap.put("wisDataGroup", wisDataGroup);
		WisDataSource entity = new WisDataSource() ;
		List<WisDataSource> wisDataSourceList = wisDataSourceService.selectAllList(entity) ;
		mmap.put("wisDataSourceList", wisDataSourceList) ;

		List<String> sourcea_tables = getDataSourceTables(wisDataGroup.getSourceaId()) ;
		List<String> sourceb_tables = getDataSourceTables(wisDataGroup.getSourcebId()) ;

		mmap.put("sourcea_tables", sourcea_tables) ;
		mmap.put("sourceb_tables", sourceb_tables) ;
		return prefix + "/editWisDataGroup";
	}

	
	/**
	 * 编辑同步组字段
	 * 
	 * @param id
	 * @param mmap
	 * @return
	 */
	@GetMapping("/editWidthCol/{id}")
	public String editWidthCol(@PathVariable("id") Long id, ModelMap mmap) {
		WisDataGroup wisDataGroup = wisDataGroupService.selectByPrimaryKey(id);
		mmap.put("wisDataGroup", wisDataGroup);
		WisDataSource wisDataSource = wisDataSourceService.selectByPrimaryKey(wisDataGroup.getSourceaId()) ;
		List<Map<String,String>> colList = JdbcColumnUtil.getColumns(wisDataSource.getDBLink(), wisDataGroup.getTabName()) ;
		
		WisDataSource wisDataSourceb = wisDataSourceService.selectByPrimaryKey(wisDataGroup.getSourcebId()) ;
		List<Map<String,String>> targetColList = JdbcColumnUtil.getColumns(wisDataSourceb.getDBLink(), wisDataGroup.getTargetName()) ;
		

		mmap.put("colList", colList);
		mmap.put("targetColList", targetColList);
		WisDataCol entity = new WisDataCol() ;
		entity.setGroupId(id);
		List<WisDataCol> wisDataColList = wisDataColService.selectAllList(entity) ;

		mmap.put("wisDataColList", wisDataColList);
		
		return prefix + "/editWisDataGroupCol";
	}
	
	
	/**
	 * 修改保存数据同步组
	 */
	@PostMapping("/delColumn")
	@ResponseBody
	public AjaxResult delColumn(Long id) {
		
		int result = wisDataColService.deleteByPrimaryKey(id) ;
		return toAjax(result);
	}
	
	/**
	 * 查看同步组字段
	 * 
	 * @param id
	 * @param mmap
	 * @return
	 */
	@GetMapping("/editCol_View/{id}")
	public String editCol_View(@PathVariable("id") Long id, ModelMap mmap) {
		WisDataGroup wisDataGroup = wisDataGroupService.selectByPrimaryKey(id);
		mmap.put("wisDataGroup", wisDataGroup);
		
		
		WisDataCol entity = new WisDataCol() ;
		entity.setGroupId(id);
		List<WisDataCol> wisDataColList = wisDataColService.selectAllList(entity) ;

		mmap.put("wisDataColList", wisDataColList);
		
		return prefix + "/viewWisDataGroupCol";
	}
	
	
	/**
	 * 修改保存数据同步组
	 */
	@RequiresPermissions("quartz:wisDataGroup:edit")
	@Log(title = "数据同步组", businessType = BusinessType.UPDATE)
	@PostMapping("/editWidthCol")
	@ResponseBody
	public AjaxResult editWidthColSave(WisDataGroup wisDataGroup,WisDataColInfo info) {
		List<WisDataCol> wisDataCols = info.getCloumn() ;
		int result = wisDataGroupService.update(wisDataGroup) ;
		int len = null != wisDataCols ? wisDataCols.size() : 0 ;
		for(int i=0 ;i< len;i++ ) {
			WisDataCol wisDataCol = wisDataCols.get(i) ;
			wisDataCol.setGroupId(wisDataGroup.getId());
			if(StringUtils.isAllEmpty(wisDataCol.getColName())) {
				continue ;
			}
			if(null == wisDataCol.getId()) {
				wisDataColService.save(wisDataCol) ;
			} else {
				wisDataColService.update(wisDataCol) ;
			}
		}
		return toAjax(result);
	}
	
	/**
	 * 修改保存数据同步组
	 */
	@RequiresPermissions("quartz:wisDataGroup:edit")
	@Log(title = "数据同步组", businessType = BusinessType.UPDATE)
	@PostMapping("/edit")
	@ResponseBody
	public AjaxResult editSave(WisDataGroup wisDataGroup) {
		wisDataGroup.setUpdateTime(new Date());
		wisDataGroup.setUpdateBy(ShiroUtils.getLoginName());
		return toAjax(wisDataGroupService.update(wisDataGroup));
	}

	/**
	 * 删除数据同步组
	 */
	@RequiresPermissions("quartz:wisDataGroup:remove")
	@Log(title = "数据同步组", businessType = BusinessType.DELETE)
	@PostMapping("/remove")
	@ResponseBody
	public AjaxResult remove(String[] ids) {
		return toAjax(wisDataGroupService.deleteByPrimaryKeys(ids));
	}

	/**
	 * 查询表数据
	 * 
	 * @param id
	 * @return
	 */
	@RequestMapping("/queryTabs")
	@ResponseBody
	public List<String> queryTabs(Long id) {
		List<String> tables = getDataSourceTables(id) ;
		return tables ;
	}

	private List<String> getDataSourceTables(Long id) {
		List<String> tables = new ArrayList<String>();
		try {
			WisDataSource source = wisDataSourceService.selectByPrimaryKey(id) ;
			String dbName = source.getDbName();
			String[] type = new String[] { "TABLE" };
			Driver driver = (Driver) Class.forName(source.getDialectEnum().getDriver()).newInstance();
			String jdbcUrl = source.getDialectEnum().urlFormat();
			jdbcUrl = MessageFormat.format(jdbcUrl, source.getHost(), source.getPort() + "", dbName);
			Properties info = new Properties();
			info.put("user", source.getUsername());
			info.put("password", source.getPassword());
			Connection conn = driver.connect(jdbcUrl, info);
			String cataLog = conn.getCatalog();
			ResultSet rs =  null ;
			if(WisDataSourceHelper.DIALECT_MYSQL.equals(source.getDialect())) {
				rs = conn.getMetaData().getTables(cataLog, dbName, "%", type);
			}
			if(WisDataSourceHelper.DIALECT_SQLSERVER.equals(source.getDialect())) {
				Statement statement = conn.createStatement() ;
				rs = statement.executeQuery("select name as TABLE_NAME from sysobjects where xtype='U' ") ;
			}
			while (rs.next()) {
				tables.add(rs.getString("TABLE_NAME"));
			}
			conn.close();
		} catch (Exception e) {
		}
		return tables ;
	}
}
